package com.shudai.largescreen.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.shudai.largescreen.pojo.Order;
import com.shudai.largescreen.pojo.User;
import com.shudai.largescreen.pojo.vo.AllCityAmount;
import com.shudai.largescreen.pojo.vo.NoRepurchaseTable;
import com.shudai.largescreen.pojo.vo.SalesmanRepurchaseVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface RepurchaseAnalysisMapper extends BaseMapper<Order> {

    @Select({
            "SELECT count(o.id) total ,sum(o.amount) finish ",
            "FROM sd_order o where o.order_state in(2,3,12) and o.on_cancel  = 0 ",
            " and o.order_time > #{startTime} "
    })
    AllCityAmount findAllAount(@Param("startTime") LocalDateTime startDate);

    @Select({
            "SELECT o.user_record_id id ,sum(o.amount) finish ",
            "FROM sd_order o where o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            " and o.order_time > #{startTime} ",
            " GROUP BY o.user_record_id"
    })
    List<AllCityAmount> findUserRecord(@Param("startTime") LocalDateTime startDate);

    @Select({
            "<script>",

            "SELECT o.user_record_id id ,count(o.id) total ",
            "FROM sd_order o where o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            " and o.user_record_id in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",
            " and o.order_time BETWEEN #{startTime} and #{endTime} ",
            " GROUP BY o.user_record_id",

            "</script>"
    })
    List<AllCityAmount> findUserRecordRate(@Param("ids") List<Long> collect,
                                           @Param("startTime") LocalDateTime startDate,
                                           @Param("endTime") LocalDateTime endTime);

    @Select({
            "<script>",
            "SELECT u.nickname salesman,u.created_at entryTime,u.id  id",
            "FROM sys_user u,sd_tool_man_info sd  ",
            "<where> 1= 1 and  u.id = sd.user_id  and u.enabled = 1 ",
            "<if test='city != null and city != \"\" '> ",
            "and sd.city = #{city} ",
            "</if> ",

            "<if test='province != null and province != \"\" '> ",
            "and sd.province = #{province} ",
            "</if> ",

            " </where>",
            "GROUP BY u.nickname ,u.created_at ,u.id  ",

            "</script> "
    })
    List<SalesmanRepurchaseVo> findSalesMan(
            @Param("province") String province,
            @Param("city") String region);


    @Select({
            "SELECT o.user_record_id userRecordId,count(o.id) purchaseCustomerNumber,sum(o.total_number) repurchaseNumber ",
            "FROM sd_order o , sys_user u ",
            "WHERE o.tool_man_id = u.id and o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "and o.user_record_id is not null and o.tool_man_id = #{id} ",
            " and o.order_time BETWEEN #{startTime} and #{endDate} ",
            "GROUP BY o.user_record_id",
    })
    List<SalesmanRepurchaseVo> findAllUserRecordIds(@Param("id") Long id,
                                                    @Param("startTime") LocalDateTime startDate,
                                                    @Param("endDate") LocalDateTime endDate);

    @Select({
            "<script>",
            "SELECT o.user_record_id userRecordId,count(o.id) repurchaseCustomerNumber,sum(o.total_number) repurchaseNumber ,o.tool_man_id id ",
            "FROM sd_order o ,sys_user u ,sys_dept t",
            "<where> 1=1 and  o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "and o.user_record_id is not null and o.tool_man_id = u.id and u.dept_id = t.id ",
            " and o.order_time BETWEEN #{startTime} and #{endDate} ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",

            "<if test='region != null and region != \"\" '> ",
            "and o.receiver_city = #{region} ",
            "</if> ",

            "<if test='teamName != null and teamName != \"\" '> ",
            "and t.name = #{teamName} ",
            "</if> ",

            "</where>",
            "GROUP BY o.user_record_id ,o.tool_man_id ",
            "</script>"
    })
    List<SalesmanRepurchaseVo> findTimesUserRecord(@Param("startTime") LocalDateTime startDate,
                                                   @Param("endDate") LocalDateTime endDate,
                                                   @Param("province") String province,
                                                   @Param("region") String region,
                                                   @Param("teamName")String teamName);

    @Select({
            "<script>",
            "SELECT count(distinct o.user_record_id)  ",
            "FROM sd_order o ",
            "<where> 1=1 and o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "and o.user_record_id is not null ",
            " and o.order_time BETWEEN #{startTime} and #{endDate} ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",

            "<if test='region != null and region != \"\" '> ",
            "and o.receiver_city = #{region} ",
            "</if> ",

            "</where>",

            "</script>"
    })
    Integer findUserNumber(@Param("province") String province,
                       @Param("region") String region,
                       @Param("startTime") LocalDateTime startDate,
                       @Param("endDate") LocalDateTime endDate
    );


    @Select({
            "<script>",
            "SELECT sum(o.amount)  ",
            "FROM sd_order o ",
            "<where> 1=1  and o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "and o.user_record_id is not null ",
            " and o.order_time BETWEEN #{startTime} and #{endDate} ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",

            "<if test='region != null and region != \"\" '> ",
            "and o.receiver_city = #{region} ",
            "</if> ",

            "</where>",

            "</script>"
    })
    Integer findUserMoney(@Param("province") String province,
                      @Param("region") String region,
                      @Param("startTime") LocalDateTime startDate,
                      @Param("endDate") LocalDateTime endDate);


    @Select({
            "<script>",
            "SELECT count(o.id)  ",
            "FROM sd_order o ",
            "<where> 1=1  and o.order_state in(2,3,12) and o.on_cancel  = 0  ",
            "and o.user_record_id is not null ",
            " and o.order_time BETWEEN #{startTime} and #{endDate} ",

            "<if test='province != null and province != \"\" '> ",
            "and o.receiver_province = #{province} ",
            "</if> ",

            "<if test='region != null and region != \"\" '> ",
            "and o.receiver_city = #{region} ",
            "</if> ",

            "</where>",

            "</script>"
    })
    int findUserM(@Param("province") String province,
                      @Param("region") String region,
                      @Param("startTime") LocalDateTime startDate,
                      @Param("endDate") LocalDateTime endDate);

//    SELECT d.name ,sd.province,sd.city,u.nickname ,u.id
//FROM sys_dept d,sd_tool_man_info sd ,sys_user u
//WHERE d.id = u.dept_id and sd.user_id = u.id
//GROUP BY d.name ,sd.province,sd.city,u.nickname,u.id
    @Select({
            "<script>",
            "SELECT d.name deptName ,sd.district district,sd.city city,u.nickname salesman ,u.id id ",

            "FROM sys_dept d,sd_tool_man_info sd ,sys_user u ",
            "WHERE d.id = u.dept_id and sd.user_id = u.id  ",
            " and u.id  in ",
            " <foreach collection=\"ids\" item=\"userId\" index=\"index\" open=\"(\" separator=\",\" close=\")\">",
            "#{userId}",
            "</foreach>",

            " GROUP BY  d.name ,sd.district,sd.city,u.nickname,u.id ",
            "</script>"
    })
    List<NoRepurchaseTable> findSalesManDetail(@Param("ids") List<Long> collect);
}
